import asyncio
import os.path
import threading
from concurrent.futures import ThreadPoolExecutor
from multiprocessing.dummy import Pool

import pythoncom
import xlwings as xls

from bin.common import Common
from conf.database import Database


def done(response):
    print("任务执行完后，回调的函数", response.result())
    pass


class ManHourStatisticalTable:
    def __init__(self, settings):
        self.TAG = '【工时统计表】'
        self.settings = settings
        self.year = self.settings.current_year
        pass

    def exec_thread(self, project_name, project_code):
        thread_name = threading.current_thread().name
        print(f'{self.TAG} {thread_name}')
        pythoncom.CoInitialize()
        project_path = os.path.join(self.settings.output_path, project_name)
        db_file_path = os.path.join(self.settings.db_path, self.settings.db_file)
        db_connect = Database.connect(db_file=db_file_path)

        app = xls.App(visible=False, add_book=False)
        app.display_alerts = False
        app.screen_updating = False

        months_query_sql = 'select distinct(month) from man_hour_summary_table \
        where project_name=? and year=?'
        months_query_data = (project_name, self.year)
        project_months = Database.query(db_connect=db_connect, sql=months_query_sql, data=months_query_data)
        for project_month in project_months:
            project_month = project_month[0]
            members_query_sql = 'select * from man_hour_summary_table where project_name=? and year=? and month=?'
            members_query_data = (project_name, self.year, project_month)
            project_members = Database.query(db_connect=db_connect, sql=members_query_sql, data=members_query_data)
            project_members_count = len(project_members)

            filename = f'☆公开☆{project_name}日工时统计表（{self.year}年{project_month}月）.xlsx'
            filepath = os.path.join(project_path, filename)
            workbook = app.books.add()
            sheet = workbook.sheets(1)
            sheet.range(f'A1').value = \
                f'__软件事业部__{project_month}月__{project_name}__日工时统计表（{self.year}年）'
            sheet.range(f'A2').value = f'项目编号：{project_code}'
            sheet.range(f'A3').value = f'日期'
            sheet.range(f'B3').value = [member[3] for member in project_members]
            sheet.range(f"{chr(ord('B') + project_members_count + 0)}3").value = '人次'
            sheet.range(f"{chr(ord('B') + project_members_count + 1)}3").value = '工时'
            sheet.range(f"{chr(ord('B') + project_members_count + 2)}3").value = '备注'
            member_count_formula = "=COUNTA(B{}:{}{})"
            result_column_offset = project_members_count - 1 if project_members_count - 1 >= 0 else 0
            sheet.range(f"{chr(ord('B') + project_members_count + 0)}4").options(transpose=True) \
                .value = [member_count_formula.format(day_row + 4, chr(ord('B') + result_column_offset),
                                                      day_row + 4) for day_row in range(31)]
            total_hour_formula = "=SUM(B{}:{}{})"
            sheet.range(f"{chr(ord('B') + project_members_count + 1)}4").options(transpose=True) \
                .value = [total_hour_formula.format(day_row + 4, chr(ord('B') + result_column_offset),
                                                    day_row + 4) for day_row in range(31)]
            total_hour_col = chr(ord('B') + project_members_count + 1)
            sheet.range(f"{total_hour_col}35").value = '=SUM({}4:{}34)'.format(total_hour_col, total_hour_col)
            sheet.range(f'A4').options(transpose=True).value = list(range(1, 32))
            member_total_hours = [member[6] for member in project_members]
            # member_work_hours = [Common.get_work_time(hour, 15) for hour in member_total_hours]
            # sheet.range(f'B4').options(transpose=True).value = member_work_hours
            sheet.range(f'B35').value = member_total_hours
            sheet.range(f'A{3 + 31 + 1}').value = '合计'
            sheet.range(f'A{3 + 31 + 3}').value = '统计（项目负责人）：'
            sheet.range(f'A{3 + 31 + 4}').value = '审核（室主任）：'
            workbook.save(filepath)
            workbook.close()
            # break
        app.quit()
        Database.close(db_connect=db_connect)
        print(f'{self.TAG} {project_path}')
        pythoncom.CoUninitialize()
        pass

    def exec(self):
        print('开始统计')

        db_file_path = os.path.join(self.settings.db_path, self.settings.db_file)
        db_connect = Database.connect(db_file=db_file_path)

        project_query_sql = 'select distinct(project_name),project_code from man_hour_summary_table where year=?'
        project_query_data = (self.year,)
        projects = Database.query(db_connect=db_connect, sql=project_query_sql, data=project_query_data)
        Database.close(db_connect=db_connect)
        max_workers = len(projects)  # 最大线程数
        thread_pool = ThreadPoolExecutor(max_workers=max_workers)  # 创建线程池
        for project in projects:
            project_name = project[0]
            project_code = project[1]
            dirname = os.path.join(self.settings.output_path, project_name)
            if not os.path.exists(dirname):
                os.mkdir(dirname)
            thread_pool.submit(self.exec_thread, project_name, project_code)
            # break
        thread_pool.shutdown(wait=True)

        print('统计完成')

        pass
